package department.fc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import department.fc.domain.Department;
import department.fc.util.DatabaseManager;
/*
 * DAO 개념
 * 데이터 접근을 목적하는 객체다. 커넥션 같은 것을 하나만 두고 여러 사용자가 DAO의 인터페이스를 
 * 사용하여 필요한 자료에 접근 하도록 하는 것이 DAO의 개념이다.
 * DB에 대한 접근을 DAO가 담당하도록 하여 데이터베이스 엑세스를 DAO에서만 하게 되면 다수의 
 * 원격 호출을 통한 오버헤드를 VO나 DTO를 통해 줄일수 있고 다수의 DB 호출문제를 해결할 수 있습니다. 
 * 또한 단순히 읽기만 하는 연산이므로 트랜잭션 간의 오버헤드를 감소할 수 있습니다. 
 */

public class DepartmentDAO {

	private static DepartmentDAO instance = new DepartmentDAO();
	
	private DatabaseManager dbManager = DatabaseManager.getInstance();
	
	private DepartmentDAO(){}
	
	public static DepartmentDAO getInstance(){
		return instance;
	}
	
	public int insertDepartment(Department department) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "insert into department values(?,?,?,?,?)";
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, department.getDepartmentNum());
			pstmt.setString(2, department.getDepartmentName());
			pstmt.setString(3, department.getChiefName());
			pstmt.setInt(4, department.getDepartmentNumOfPeople());
			pstmt.setString(5, department.getLocation());
			cnt = pstmt.executeUpdate();
		}finally{
			dbManager.close(conn, pstmt);
		}
		return cnt;
	}
	public int updateDepartmentByNum(Department department) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update department set department_name=?, chief_name=?, department_people=?, location=? where department_num=?";
		int cnt = 0;
		
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, department.getDepartmentName());
			pstmt.setString(2, department.getChiefName());
			pstmt.setInt(3, department.getDepartmentNumOfPeople());
			pstmt.setString(4, department.getLocation());
			pstmt.setInt(5, department.getDepartmentNum());
			cnt = pstmt.executeUpdate();
		}finally{
			dbManager.close(conn, pstmt);
			
		}
		return cnt;
	}
	public ArrayList<Department> selectDepartmentList() throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		String sql = "select department_num, department_name, chief_name, department_people, location from department order by department_num";
		ArrayList<Department> list = new ArrayList<Department>();
		
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while(rset.next()){
				int num = rset.getInt(1);
				String dname = rset.getString(2);
				String cname = rset.getString(3);
				int people = rset.getInt(4);
				String location = rset.getString(5);
				Department dept = new Department(num, dname, cname, people, location);
				list.add(dept);
			}
		}finally{
			dbManager.close(conn, pstmt, rset);
		}
		return list;
	}
	
	public int deleteDepartmentByNum(int departmentNum) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from department where department_num=?";
		int cnt = 0;
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, departmentNum);
			
			cnt = pstmt.executeUpdate();
			
		}finally{
			dbManager.close(conn, pstmt);
		}
		return cnt;
	}
	
	public ArrayList<Department> selectDepartmentByLocation(String location)throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		String sql = "select * from department where location=? order by department_num";
		ArrayList<Department> list = new ArrayList<Department>();
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, location);
			rset = pstmt.executeQuery();
			while(rset.next()){
				
				list.add(new Department(rset.getInt(1), rset.getString(2), rset.getString(3), rset.getInt(4), location));
			}
			
		}finally{
			dbManager.close(conn, pstmt, rset);
		}
		return list;
	}
	
	public Department selectDepartmentByNum(int departmentNum) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Department dept = null;
		String sql = "select department_name, chief_name, department_people, location from department  where department_num=?";
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, departmentNum);
			
			rset = pstmt.executeQuery();
			if(rset.next()){
				dept = new Department(departmentNum, rset.getString(1), rset.getString(2), rset.getInt(3), rset.getString(4));
			}
		}finally{
			dbManager.close(conn, pstmt, rset);
		}
		return dept;
	}	
}